package com.matt.repository;

import com.matt.domain.Children;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;

/**
 * *******************************
 * Created by Ron.
 * form com.matt
 * on 2017/7/13
 * *******************************
 */
public interface ChildrenRepository extends JpaRepository<Children, String> , JpaSpecificationExecutor<Children> {

    Children findFirstByIdAndAreaId(String childrenId, String areaId);
    Children findFirstById(String childrenId);


    Children findByCrmClienteleId(String clienteleId);

    Children findByNameAndBirthdayAndAreaId(String name, Date birthday, String areaId);

    Children findByIdAndAreaId(String childrenId, String areaId);

    List<Children> findByAreaIdAndStatus(String areaId, Integer status);

    List<Children> findByAreaIdAndStatusAndDeleted(String areaId, Integer status, Boolean isHidden);

    List<Children> findByAreaIdAndDeleted(String areaId, Boolean deleted);

    @Query(value = "select c from Children c where c.areaId=:areaId and c.name like CONCAT('%', :name, '%') and c.deleted = :deleted")
    List<Children> findByAreaIdAndNameLikeAndDeleted(@Param("areaId") String areaId, @Param("name")String name, @Param("deleted")Boolean deleted);

    List<Children> findByIdIn(String[] childrenIds);

    List<Children> findAllByAreaId(String areaId);

    List<Children> findAllByAreaIdAndIdIn(String areaId, String[] childrenIds);
    List<Children> findAllByAreaIdAndIdInAndStatus(String areaId, List<String> childrenIds, Integer status);

    Integer countByAreaIdAndCreatedBetween(String areaId, Date begin, Date end);

    List<Children> findByNameAndAreaId(String childrenName, String areaId);

    Children findByCrmClienteleIdAndNameAndAreaId(String clienteleId,String name,String areaId);


    @Query(value = "SELECT distinct ch.* from rel_classes_children r " +
            " left JOIN classes cl on r.classesId = cl.id " +
            " left join children ch on r.childrenId = ch.id " +
            " where cl.areaId = :areaId  and cl.name like concat('%', :className, '%')", nativeQuery = true)
    List<Children> findAllChildrenByAreaIdAndClassNameLike(@Param("areaId") String areaId, @Param("className")String className);

    @Query(value = "SELECT distinct ch.* from rel_classes_children r " +
            " left JOIN classes cl on r.classesId = cl.id " +
            " left join children ch on r.childrenId = ch.id " +
            " where cl.areaId = :areaId  and cl.id = :classId ", nativeQuery = true)
    List<Children> findAllChildrenByAreaIdAndClassId(@Param("areaId") String areaId, @Param("classId")String className);


    @Query(value = "SELECT ch.* from rel_classes_children r " +
            " left JOIN classes cl on r.classesId = cl.id " +
            " left join children ch on r.childrenId = ch.id " +
            " where cl.areaId = ?1  and cl.id = ?2 ORDER BY ch.created",
            countQuery = "SELECT count(*) from rel_classes_children r " +
                    " left JOIN classes cl on r.classesId = cl.id " +
                    " where cl.areaId = ?1  and cl.id = ?2",
            nativeQuery = true)
    Page<Children> findAllChildrenByAreaIdAndClassId( String areaId, String classId, Pageable pageable);

    @Query(value = "select * from children c where c.id in (" +
            "select childrenId from children_extend_info e where e.guardianPhone = :guardianPhone" +
            ") and c.deleted is false", nativeQuery = true)
    List<Children> findAllByParentPhoneAndDeletedIsFalse(@Param("guardianPhone") String phone);


    List<Children> findByStaffIdAndDeletedIsFalseOrderByCreatedDesc(String staffId);
    List<Children> findByStaffIdAndDeletedIsFalseOrderByBirthdayDesc(String staffId);
    List<Children> findByStaffIdAndDeletedIsFalseOrderByNextEvaluateDateDesc(String staffId);
    List<Children> findByStaffIdAndDeletedIsFalseOrderByLastEvaluateDateDesc(String staffId);

    List<Children> findByStaffIdAndNameLikeAndDeletedIsFalseOrderByCreatedDesc(String staffId, String name);
    List<Children> findByStaffIdAndNameLikeAndDeletedIsFalseOrderByBirthdayDesc(String staffId, String name);
    List<Children> findByStaffIdAndNameLikeAndDeletedIsFalseOrderByNextEvaluateDateDesc(String staffId, String name);
    List<Children> findByStaffIdAndNameLikeAndDeletedIsFalseOrderByLastEvaluateDateDesc(String staffId, String name);

    //查询当月生日学生
    @Query(value = "SELECT c.* FROM children c left join rel_classes_children cc  on c.id = cc.childrenId  where MONTH(c.birthday)  = ?2 and  c.aid = ?1 and c.status = 1 " +
            " and if(?3 != '', cc.classesId = ?3 , 1=1 )  GROUP BY c.id", nativeQuery = true)
    List<Children> findByAreaIdAndBirthday(String areaId, String month,String classesId);

    //查询学生指定时间在园状态
    @Query(value = "SELECT c.* FROM `children`  c INNER JOIN children_extend_info cei  on cei.childrenId = c.id left join rel_classes_children cc  on c.id = cc.childrenId " +
            "WHERE    c.status = 1 and c.aid = ?1 AND if(?2 = '1', checkStatus = '1' AND  DATE_FORMAT(cei.checkTime,'%Y-%m-%d') = DATE_FORMAT(?3,'%Y-%m-%d'),   ( DATE_FORMAT(cei.checkTime,'%Y-%m-%d') != DATE_FORMAT(?3,'%Y-%m-%d') or cei.checkStatus != '1' ) ) and if(?4 != '', cc.classesId = ?4 , 1=1 )  GROUP BY c.id  ", nativeQuery = true)
    List<Children> findByAreaIdAndCheckStatusAndCheckTime(String areaId, String checkStatus,String checkTime,String classesId);


    //总学生数量
    @Query(value = "SELECT count(id) FROM  children where  status = 1  AND if(?1 != -1, gender = ?1 , 1=1 ) and aid in (?2)", nativeQuery = true)
    Integer getCount(Integer gender, List<String> cid);

    //总集团园学生
    List<Children> findAllByStatusAndAreaIdIn(Integer status, String[] areaIds);

    //总学生数量
    @Query(value = "SELECT count(id) FROM  children where  status = 1  AND  gender = ?1   and aid  =  ?2", nativeQuery = true)
    Integer getGenderCount(Integer gender, String aid);
}
